package vip.xiaonuo.smzq.modular.synchronize.service;

import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.net.URLDecoder;
import cn.hutool.json.JSONUtil;
import com.aliyun.oss.ServiceException;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.google.common.collect.Lists;
import com.healthmarketscience.jackcess.*;
import org.apache.commons.io.FileUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import vip.xiaonuo.auth.core.pojo.SaBaseLoginUser;
import vip.xiaonuo.auth.core.util.StpClientUtil;
import vip.xiaonuo.auth.core.util.StpLoginUserUtil;
import vip.xiaonuo.common.exception.CommonException;
import vip.xiaonuo.smzq.core.tools.connection.MdbUtils;
import vip.xiaonuo.smzq.core.tools.databas.DatabaseConnectionPool;
import vip.xiaonuo.smzq.core.tools.file.PathUtils;
import vip.xiaonuo.smzq.modular.deom.NumEntity;
import vip.xiaonuo.smzq.modular.drkqs.entity.*;
import vip.xiaonuo.smzq.modular.drkqs.service.*;
import vip.xiaonuo.smzq.modular.hisi.service.IKsZyclhzService;
import vip.xiaonuo.smzq.modular.kcl.entity.*;
import vip.xiaonuo.smzq.modular.kcl.entity.vo.ExportTable;
import vip.xiaonuo.smzq.modular.kcl.service.*;
import vip.xiaonuo.smzq.modular.synchronize.entity.vo.FieldVo;
import vip.xiaonuo.smzq.modular.synchronize.mapper.TableFieldsMapper;
import vip.xiaonuo.smzq.modular.synchronize.service.impl.ISynchDataService;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.sql.*;
import java.util.*;
import java.util.concurrent.*;
import java.util.stream.Collectors;

@Service
public class SynchDataServiceImpl implements ISynchDataService {

    @Resource
    private ICsbService iCsbService;

    @Resource
    private IKclBgService iKclBgService;

    @Resource
    private IKclClService iKclClService;

    @Resource
    private IKclDztjService iKclDztjService;

    @Resource
    private IKclKsService iKclKsService;

    @Resource
    private IKclKsjjService iKclKsjjService;

    @Resource
    private IKclSzService iKclSzService;

    @Resource
    private IKclZbService iKclZbService;

    @Resource
    private IKclZyclService iKclZyclService;

    @Resource
    private IBazyclService iBazyclService;

    @Resource
    private IBgService iBgService;

    @Resource
    private ICxhService iCxhService;

    @Resource
    private IDyxlService iDyxlService;

    @Resource
    private IDztjService iDztjService;

    @Resource
    private IKcpService iKcpService;

    @Resource
    private IKqbdService iKqbdService;

    @Resource
    private IKsService iKsService;

    @Resource
    private IKsjjService iKsjjService;

    @Resource
    private IKsqkbdbService iKsqkbdbService;

    @Resource
    private ISjkcsService iSjkcsService;

    @Resource
    private IYfService iYfService;

    @Resource
    private IZbService iZbService;

    @Resource
    private IZhhsService iZhhsService;

    @Resource
    private IZlService iZlService;

    @Resource
    private IZyclService iZyclService;


    @Resource
    private ISjglService iSjglService;//数据管理mdb导入

    @Resource
    private ICkqsqdjService iCkqsqdjService;//采矿权登记基本信息
    @Resource
    private ITkqsqdjService iTkqsqdjService;//探矿权登记基本信息
    @Resource
    private IKsshjlService iKsshjlService;//矿山审核记录

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    protected HttpServletResponse response;

    @Autowired
    private TableFieldsMapper tableFieldsMapper; //数据表,字段

    @Resource
    private IKsZyclhzService iKsZyclhzService; //同步矿山汇总数据

    @Value("${spring.datasource.dynamic.datasource.master.url}")
    String DB_URL;
    @Value("${spring.datasource.dynamic.datasource.master.username}")
    String USER;
    @Value("${spring.datasource.dynamic.datasource.master.password}")
    String PASS;
    @Value("${file.base}")
    String baseFilePath;

    //创建线程池
    private final ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());

    public void tbDataOld(String filePath, Integer nd) {
        int groupSize = 100;

        try {

            System.out.println("开始导入备案资源储量 kcl_bazycl");
            //备案资源储量 kcl_bazycl
            String bazyclSql = "select * from kcl_bazycl";
            String sql = "select count(*) from kcl_bazycl";
            if (nd != null) {
                bazyclSql = bazyclSql + " where nd = " + nd;
                sql = sql + " where nd = " + nd;
            }
            int bazyclCount = MdbUtils.selectCountr(filePath, sql);
            // 计算可以整除的组数
            int bazyclGroup = bazyclCount / groupSize;
            // 计算剩余的数量
            int remainder = bazyclCount % groupSize;
            System.out.println(bazyclCount);


            QueryWrapper<Bazycl> queryWrapper = new QueryWrapper<>();
            if (nd != null) {
                queryWrapper.eq("nd", nd);
            }
            List<Bazycl> oldBazyclList = iBazyclService.selectList(queryWrapper);


            for (int i = 0; i < bazyclGroup; i++) {
                List<Bazycl> bazyclList = MdbUtils.executeQuery(filePath, bazyclSql + " limit " + groupSize * i + "," + groupSize, Bazycl.class);
                if (!bazyclList.isEmpty()) {
                    //  List<Bazycl> oldBazyclList = iBazyclService.list();
                    if (!oldBazyclList.isEmpty()) {
                        List<Bazycl> newKsList = new ArrayList<>();
                        for (Bazycl bazycl : bazyclList) {
                            for (Bazycl old : oldBazyclList) {
                                if (bazycl.getNd() != null && old.getNd() != null && bazycl.getDjflbh() != null && old.getDjflbh() != null && bazycl.getKqbh() != null && old.getKqbh() != null) {
                                    if (bazycl.getNd().equals(old.getNd()) &&
                                            bazycl.getDjflbh().equals(old.getDjflbh()) &&
                                            bazycl.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(bazycl);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            bazyclList = bazyclList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    boolean bazycl = iBazyclService.saveBatch(bazyclList, groupSize);
//                    if (bazycl) {
//                        System.out.println("备案资源储量 kcl_bazycl 导入成功" + bazyclList.size() + "条数据");
//                    }

                }

            }

            if (remainder > 0) {
                List<Bazycl> bazyclList = MdbUtils.executeQuery(filePath, bazyclSql + " limit " + groupSize * bazyclGroup + "," + remainder, Bazycl.class);
                if (!bazyclList.isEmpty()) {
                    //List<Bazycl> oldBazyclList = iBazyclService.list();
                    if (!oldBazyclList.isEmpty()) {
                        List<Bazycl> newKsList = new ArrayList<>();
                        for (Bazycl bazycl : bazyclList) {
                            for (Bazycl old : oldBazyclList) {
                                if (bazycl.getNd() != null && old.getNd() != null && bazycl.getDjflbh() != null && old.getDjflbh() != null && bazycl.getKqbh() != null && old.getKqbh() != null) {
                                    if (bazycl.getNd().equals(old.getNd()) &&
                                            bazycl.getDjflbh().equals(old.getDjflbh()) &&
                                            bazycl.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(bazycl);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            bazyclList = bazyclList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    boolean bazycl = iBazyclService.saveBatch(bazyclList, groupSize);
//                    if (bazycl) {
//                        System.out.println("备案资源储量 kcl_bazycl 导入成功" + bazyclList.size() + "条数据");
//                    }

                }
            }
            System.out.println("备案资源储量 kcl_bazycl 导入成功" + bazyclCount + "条数据");

            //报告kcl_bg
            System.out.println("开始导入报告 kcl_bg");

            String bgSql = "select * from kcl_bg";
            String sql1 = "select count(*) from kcl_bg";
            //  String sql="select COUNT(*) FROM kcl_bg";
            if (nd != null) {
                bgSql = bgSql + " where nd = " + nd;
                sql1 = sql1 + " where nd = " + nd;
            }
            int bgCount = MdbUtils.selectCountr(filePath, sql1);
            System.out.println(bgCount);
            // 计算可以整除的组数
            int bgGroup = bgCount / groupSize;
            // 计算剩余的数量
            int bgRem = bgCount % groupSize;

            QueryWrapper<Bg> queryWrapperbg = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperbg.eq("nd", nd);
            }
            List<Bg> oldBgList = iBgService.selectList(queryWrapperbg);

            for (int i = 0; i < bgGroup; i++) {
                List<Bg> bgList = MdbUtils.executeQuery(filePath, bgSql + " limit " + groupSize * i + "," + groupSize, Bg.class);
                if (!bgList.isEmpty()) {
                    if (!oldBgList.isEmpty()) {
                        List<Bg> newKsList = new ArrayList<>();
                        for (Bg bg : bgList) {
                            for (Bg old : oldBgList) {
                                if (bg.getNd() != null && old.getNd() != null && bg.getDjflbh() != null && old.getDjflbh() != null && bg.getKqbh() != null && old.getKqbh() != null) {
                                    if (bg.getNd().equals(old.getNd()) &&
                                            bg.getDjflbh().equals(old.getDjflbh()) &&
                                            bg.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(bg);
                                    }
                                }
                            }

                        }
                        if (!newKsList.isEmpty()) {
                            bgList = bgList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }

                    }

                    boolean bg = iBgService.saveBatch(bgList, groupSize);
//                    if (bg) {
//                        System.out.println("报告 kcl_bg 成功导入 " + bgList.size() + " 条");
//                    }

                }
            }

            if (bgRem > 0) {
                List<Bg> bgList = MdbUtils.executeQuery(filePath, bgSql + " limit " + groupSize * bazyclGroup + "," + bgRem, Bg.class);
                if (!bgList.isEmpty()) {

                    if (!oldBgList.isEmpty()) {
                        List<Bg> newKsList = new ArrayList<>();
                        for (Bg bg : bgList) {

                            for (Bg old : oldBgList) {
                                if (bg.getNd() != null && old.getNd() != null && bg.getDjflbh() != null && old.getDjflbh() != null && bg.getKqbh() != null && old.getKqbh() != null) {
                                    if (bg.getNd().equals(old.getNd()) &&
                                            bg.getDjflbh().equals(old.getDjflbh()) &&
                                            bg.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(bg);
                                    }
                                }
                            }

                        }
                        if (!newKsList.isEmpty()) {
                            bgList = bgList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    boolean bg = iBgService.saveBatch(bgList, groupSize);
//                    if (bg) {
//                        System.out.println("报告 kcl_bg 成功导入 " + bgList.size() + " 条");
//                    }

                }
            }
            System.out.println("报告 kcl_bg 成功导入 " + bgCount + " 条");
            //采选环kcl_cxh
            System.out.println("开始导入采选环 kcl_cxh");
            String cxhSql = "select * from kcl_cxh";
            String sql2 = "select count(*) from kcl_cxh";
            if (nd != null) {
                cxhSql = cxhSql + " where nd = " + nd;
                sql2 = sql2 + " where nd = " + nd;
            }
            int cxhCount = MdbUtils.selectCountr(filePath, sql2);
            // 计算可以整除的组数
            int cxhGroup = cxhCount / groupSize;
            // 计算剩余的数量
            int cxhrem = cxhCount % groupSize;

            QueryWrapper<Cxh> queryWrappercxh = new QueryWrapper<>();
            if (nd != null) {
                queryWrappercxh.eq("nd", nd);
            }
            List<Cxh> oldCxhList = iCxhService.selectList(queryWrappercxh);


            for (int i = 0; i < cxhGroup; i++) {
                List<Cxh> cxhList = MdbUtils.executeQuery(filePath, cxhSql + " limit " + groupSize * i + "," + groupSize, Cxh.class);
                if (!cxhList.isEmpty()) {
                    //List<Cxh> oldCxhList = iCxhService.list();
                    if (!oldCxhList.isEmpty()) {
                        List<Cxh> newKsList = new ArrayList<>();
                        for (Cxh cxh : cxhList) {
                            for (Cxh old : oldCxhList) {
                                if (cxh.getNd() != null && old.getNd() != null && cxh.getDjflbh() != null && old.getDjflbh() != null && cxh.getKqbh() != null && old.getKqbh() != null) {

                                    if (cxh.getNd().equals(old.getNd()) &&
                                            cxh.getDjflbh().equals(old.getDjflbh()) &&
                                            cxh.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(cxh);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            cxhList = cxhList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    boolean cxh = iCxhService.saveBatch(cxhList, groupSize);
//                    if (cxh) {
//                        System.out.println("采选环 cxh 成功导入 " + cxhList.size() + " 条");
//                    }
                }

            }
            if (cxhrem > 0) {
                List<Cxh> cxhList = MdbUtils.executeQuery(filePath, cxhSql + " limit " + groupSize * cxhGroup + "," + cxhrem, Cxh.class);
                if (!cxhList.isEmpty()) {
                    //List<Cxh> oldCxhList = iCxhService.list();
                    if (!oldCxhList.isEmpty()) {
                        List<Cxh> newKsList = new ArrayList<>();
                        for (Cxh cxh : cxhList) {
                            for (Cxh old : oldCxhList) {

                                if (cxh.getNd() != null && old.getNd() != null && cxh.getDjflbh() != null && old.getDjflbh() != null && cxh.getKqbh() != null && old.getKqbh() != null) {

                                    if (cxh.getNd().equals(old.getNd()) &&
                                            cxh.getDjflbh().equals(old.getDjflbh()) &&
                                            cxh.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(cxh);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            cxhList = cxhList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    boolean cxh = iCxhService.saveBatch(cxhList, groupSize);
//                    if (cxh) {
//                        System.out.println("采选环 cxh 成功导入 " + cxhList.size() + " 条");
//                    }
                }
            }
            System.out.println("采选环 cxh 成功导入 " + cxhCount + " 条");


            //地质条件kcl_dztj
            String dztjSql = "select * from kcl_dztj";
            String sql3 = "select count(*) from kcl_dztj";
            if (nd != null) {
                dztjSql = dztjSql + " where nd = " + nd;
                sql3 = sql3 + " where nd = " + nd;
            }

            int dztjCount = MdbUtils.selectCountr(filePath, sql3);
            // 计算可以整除的组数
            int dztiGroup = dztjCount / groupSize;
            // 计算剩余的数量
            int dztjrem = dztjCount % groupSize;

            QueryWrapper<Dztj> queryWrapperdztj = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperdztj.eq("nd", nd);
            }
            List<Dztj> oldDztjList = iDztjService.selectList(queryWrapperdztj);


            for (int i = 0; i < dztiGroup; i++) {
                List<Dztj> dztjList = MdbUtils.executeQuery(filePath, dztjSql + " limit " + groupSize * i + "," + groupSize, Dztj.class);
                if (!dztjList.isEmpty()) {
                    // List<Dztj> oldDztjList = iDztjService.list();
                    if (!oldDztjList.isEmpty()) {
                        List<Dztj> newKsList = new ArrayList<>();
                        for (Dztj dztj : dztjList) {
                            for (Dztj old : oldDztjList) {
                                if (dztj.getNd() != null && old.getNd() != null && dztj.getDjflbh() != null && old.getDjflbh() != null && dztj.getKqbh() != null && old.getKqbh() != null) {

                                    if (dztj.getNd().equals(old.getNd()) &&
                                            dztj.getDjflbh().equals(old.getDjflbh()) &&
                                            dztj.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(dztj);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            dztjList = dztjList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iDztjService.saveBatch(dztjList, groupSize);
                }
            }

            if (dztjrem > 0) {
                List<Dztj> dztjList = MdbUtils.executeQuery(filePath, dztjSql + " limit " + groupSize * dztiGroup + "," + dztjrem, Dztj.class);
                if (!dztjList.isEmpty()) {
                    // List<Dztj> oldDztjList = iDztjService.list();
                    if (!oldDztjList.isEmpty()) {
                        List<Dztj> newKsList = new ArrayList<>();
                        for (Dztj dztj : dztjList) {
                            for (Dztj old : oldDztjList) {
                                if (dztj.getNd() != null && old.getNd() != null && dztj.getDjflbh() != null && old.getDjflbh() != null && dztj.getKqbh() != null && old.getKqbh() != null) {

                                    if (dztj.getNd().equals(old.getNd()) &&
                                            dztj.getDjflbh().equals(old.getDjflbh()) &&
                                            dztj.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(dztj);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            dztjList = dztjList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iDztjService.saveBatch(dztjList, groupSize);
                }
            }

            System.out.println("地质条件 kcl_dztj 成功导入 " + dztjCount + " 条");


            //矿产品kcl_kcp
            String kcpSql = "select * from kcl_kcp";
            String sql4 = "select count(*) from kcl_kcp";
            if (nd != null) {
                kcpSql = kcpSql + " where nd = " + nd;
                sql4 = sql4 + " where nd = " + nd;
            }

            int kcpCount = MdbUtils.selectCountr(filePath, sql4);
            // 计算可以整除的组数
            int kcpGroup = kcpCount / groupSize;
            // 计算剩余的数量
            int kcprem = kcpCount % groupSize;


            QueryWrapper<Kcp> queryWrapperKcp = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperKcp.eq("nd", nd);
            }
            List<Kcp> oldKcpList = iKcpService.selectList(queryWrapperKcp);

            for (int i = 0; i < kcpGroup; i++) {
                List<Kcp> kcpList = MdbUtils.executeQuery(filePath, kcpSql + " limit " + groupSize * i + "," + groupSize, Kcp.class);
                if (!kcpList.isEmpty()) {
                    // List<Kcp> oldKcpList = iKcpService.list();
                    if (!oldKcpList.isEmpty()) {
                        List<Kcp> newKsList = new ArrayList<>();
                        for (Kcp kcp : kcpList) {
                            for (Kcp old : oldKcpList) {
                                if (kcp.getNd() != null && old.getNd() != null && kcp.getDjflbh() != null && old.getDjflbh() != null && kcp.getKqbh() != null && old.getKqbh() != null) {

                                    if (kcp.getNd().equals(old.getNd()) &&
                                            kcp.getDjflbh().equals(old.getDjflbh()) &&
                                            kcp.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(kcp);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            kcpList = kcpList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iKcpService.saveBatch(kcpList, groupSize);
                }
            }

            if (kcprem > 0) {
                List<Kcp> kcpList = MdbUtils.executeQuery(filePath, kcpSql + " limit " + groupSize * kcpGroup + "," + kcprem, Kcp.class);
                if (!kcpList.isEmpty()) {
                    //   List<Kcp> oldKcpList = iKcpService.list();
                    if (!oldKcpList.isEmpty()) {
                        List<Kcp> newKsList = new ArrayList<>();
                        for (Kcp kcp : kcpList) {
                            for (Kcp old : oldKcpList) {
                                if (kcp.getNd() != null && old.getNd() != null && kcp.getDjflbh() != null && old.getDjflbh() != null && kcp.getKqbh() != null && old.getKqbh() != null) {

                                    if (kcp.getNd().equals(old.getNd()) &&
                                            kcp.getDjflbh().equals(old.getDjflbh()) &&
                                            kcp.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(kcp);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            kcpList = kcpList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iKcpService.saveBatch(kcpList, groupSize);
                }
            }

            System.out.println("矿产品 kcl_kcp 成功导入 " + kcpCount + " 条");


            //矿区变动kcl_kqbd
            String kqbdSql = "select * from kcl_kqbd";
            String sql5 = "select count(*) from kcl_kqbd";

            if (nd != null) {
                kqbdSql = kqbdSql + " where nd = " + nd;
                sql5 = sql5 + " where nd = " + nd;

            }

            int kqbdCount = MdbUtils.selectCountr(filePath, sql5);
            // 计算可以整除的组数
            int kqbdGroup = bazyclCount / groupSize;
            // 计算剩余的数量
            int kqbdrem = bazyclCount % groupSize;

            QueryWrapper<Kqbd> queryWrapperKqbd = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperKqbd.eq("nd", nd);
            }
            List<Kqbd> oldkqbdList = iKqbdService.selectList(queryWrapperKqbd);


            for (int i = 0; i < kqbdGroup; i++) {
                List<Kqbd> kqbdList = MdbUtils.executeQuery(filePath, kqbdSql + " limit " + groupSize * i + "," + groupSize, Kqbd.class);
                if (!kqbdList.isEmpty()) {
                    //List<Kqbd> oldkqbdList = iKqbdService.list();
                    if (!oldkqbdList.isEmpty()) {
                        List<Kqbd> newKsList = new ArrayList<>();
                        for (Kqbd kqbd : kqbdList) {
                            for (Kqbd old : oldkqbdList) {
                                if (kqbd.getNd() != null && old.getNd() != null && kqbd.getDjflbh() != null && old.getDjflbh() != null && kqbd.getKqbh() != null && old.getKqbh() != null) {

                                    if (kqbd.getNd().equals(old.getNd()) &&
                                            kqbd.getDjflbh().equals(old.getDjflbh()) &&
                                            kqbd.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(kqbd);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            kqbdList = kqbdList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iKqbdService.saveBatch(kqbdList, groupSize);
                }

            }


            if (kqbdrem > 0) {
                List<Kqbd> kqbdList = MdbUtils.executeQuery(filePath, kqbdSql + " limit " + groupSize * kqbdGroup + "," + kqbdrem, Kqbd.class);
                if (!kqbdList.isEmpty()) {
                    //List<Kqbd> oldkqbdList = iKqbdService.list();
                    if (!oldkqbdList.isEmpty()) {
                        List<Kqbd> newKsList = new ArrayList<>();
                        for (Kqbd kqbd : kqbdList) {
                            for (Kqbd old : oldkqbdList) {
                                if (kqbd.getNd() != null && old.getNd() != null && kqbd.getDjflbh() != null && old.getDjflbh() != null && kqbd.getKqbh() != null && old.getKqbh() != null) {

                                    if (kqbd.getNd().equals(old.getNd()) &&
                                            kqbd.getDjflbh().equals(old.getDjflbh()) &&
                                            kqbd.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(kqbd);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            kqbdList = kqbdList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iKqbdService.saveBatch(kqbdList, groupSize);
                }
            }

            System.out.println("矿区变动 kcl_kqbd 成功导入" + kqbdCount + " 条");


            //矿山基本信息kcl_ks
            String ksSql = "select * from kcl_ks";
            String sql6 = "select count(*) from kcl_ks";
            if (nd != null) {
                ksSql = ksSql + " where nd = " + nd;
                sql6 = sql6 + " where nd = " + nd;
            }
            int ksCount = MdbUtils.selectCountr(filePath, sql6);
            // 计算可以整除的组数
            int ksGroup = ksCount / groupSize;
            // 计算剩余的数量
            int ksrem = ksCount % groupSize;

            QueryWrapper<Ks> queryWrapperKs = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperKs.eq("nd", nd);
            }
            List<Ks> oldKsList = iKsService.selectList(queryWrapperKs);


            for (int i = 0; i < ksGroup; i++) {
                List<Ks> ksList = MdbUtils.executeQuery(filePath, ksSql + " limit " + groupSize * i + "," + groupSize, Ks.class);
                if (!ksList.isEmpty()) {
                    // List<Ks> oldKsList = iKsService.list();
                    if (!oldKsList.isEmpty()) {
                        List<Ks> newKsList = new ArrayList<>();
                        for (Ks ks : ksList) {
                            for (Ks old : oldKsList) {
                                if (ks.getNd() != null && old.getNd() != null && ks.getDjflbh() != null && old.getDjflbh() != null && ks.getKqbh() != null && old.getKqbh() != null) {

                                    if (ks.getNd().equals(old.getNd()) &&
                                            ks.getDjflbh().equals(old.getDjflbh()) &&
                                            ks.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(ks);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            ksList = ksList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iKsService.saveBatch(ksList, groupSize);
                }
            }

            if (ksrem > 0) {
                List<Ks> ksList = MdbUtils.executeQuery(filePath, ksSql + " limit " + groupSize * ksGroup + "," + ksrem, Ks.class);
                if (!ksList.isEmpty()) {
                    //  List<Ks> oldKsList = iKsService.list();
                    if (!oldKsList.isEmpty()) {
                        List<Ks> newKsList = new ArrayList<>();
                        for (Ks ks : ksList) {
                            for (Ks old : oldKsList) {
                                if (ks.getNd() != null && old.getNd() != null && ks.getDjflbh() != null && old.getDjflbh() != null && ks.getKqbh() != null && old.getKqbh() != null) {

                                    if (ks.getNd().equals(old.getNd()) &&
                                            ks.getDjflbh().equals(old.getDjflbh()) &&
                                            ks.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(ks);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            ksList = ksList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iKsService.saveBatch(ksList, groupSize);
                }
            }
            System.out.println("矿山基本信息 kcl_ks 成功导入" + ksCount + " 条");


            //矿山经济kcl_ksjj
            String ksJjSql = "select * from kcl_ksjj";
            String sql7 = "select count(*) from kcl_ksjj";
            if (nd != null) {
                ksJjSql = ksJjSql + " where nd = " + nd;
                sql7 = sql7 + " where nd = " + nd;
            }

            int ksJjCount = MdbUtils.selectCountr(filePath, sql7);
            // 计算可以整除的组数
            int ksjjGroup = ksJjCount / groupSize;
            // 计算剩余的数量
            int ksjjrem = ksJjCount % groupSize;

            QueryWrapper<Ksjj> queryWrapperKsjj = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperKsjj.eq("nd", nd);
            }
            List<Ksjj> oldKsJjList = iKsjjService.selectList(queryWrapperKsjj);


            for (int i = 0; i < ksjjGroup; i++) {
                List<Ksjj> ksJjList = MdbUtils.executeQuery(filePath, ksJjSql + " limit " + groupSize * i + "," + groupSize, Ksjj.class);
                if (!ksJjList.isEmpty()) {
                    // List<Ksjj> oldKsJjList = iKsjjService.list();
                    if (!oldKsJjList.isEmpty()) {
                        List<Ksjj> newKsList = new ArrayList<>();
                        for (Ksjj ksjj : ksJjList) {
                            for (Ksjj old : oldKsJjList) {
                                if (ksjj.getNd() != null && old.getNd() != null && ksjj.getDjflbh() != null && old.getDjflbh() != null && ksjj.getKqbh() != null && old.getKqbh() != null) {

                                    if (ksjj.getNd().equals(old.getNd()) &&
                                            ksjj.getDjflbh().equals(old.getDjflbh()) &&
                                            ksjj.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(ksjj);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            ksJjList = ksJjList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iKsjjService.saveBatch(ksJjList, groupSize);
                }
            }
            if (ksjjrem > 0) {
                List<Ksjj> ksJjList = MdbUtils.executeQuery(filePath, ksJjSql + " limit " + groupSize * ksjjGroup + "," + ksjjrem, Ksjj.class);
                if (!ksJjList.isEmpty()) {
                    // List<Ksjj> oldKsJjList = iKsjjService.list();
                    if (!oldKsJjList.isEmpty()) {
                        List<Ksjj> newKsList = new ArrayList<>();
                        for (Ksjj ksjj : ksJjList) {
                            for (Ksjj old : oldKsJjList) {
                                if (ksjj.getNd() != null && old.getNd() != null && ksjj.getDjflbh() != null && old.getDjflbh() != null && ksjj.getKqbh() != null && old.getKqbh() != null) {

                                    if (ksjj.getNd().equals(old.getNd()) &&
                                            ksjj.getDjflbh().equals(old.getDjflbh()) &&
                                            ksjj.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(ksjj);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            ksJjList = ksJjList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iKsjjService.saveBatch(ksJjList, groupSize);
                }
            }
            System.out.println("矿山经济 kcl_ksjj 成功导入" + ksJjCount + " 条");


            //矿山变动表kcl_ksqkbdb
            String ksQkbdbSql = "select * from kcl_ksqkbdb";
            String sql8 = "select count(*) from kcl_ksqkbdb";
            if (nd != null) {
                ksQkbdbSql = ksQkbdbSql + " where nd = " + nd;
                sql8 = sql8 + " where nd = " + nd;
            }
            int ksQkbdbCount = MdbUtils.selectCountr(filePath, sql8);
            int ksQkbdbGroup = ksQkbdbCount / groupSize;
            int ksQkbdbrem = ksQkbdbCount % groupSize;

            QueryWrapper<Ksqkbdb> queryWrapperKsqkbdb = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperKsqkbdb.eq("nd", nd);
            }
            List<Ksqkbdb> oldKsQkbdbList = iKsqkbdbService.selectList(queryWrapperKsqkbdb);


            for (int i = 0; i < ksQkbdbGroup; i++) {
                List<Ksqkbdb> ksQkbdbList = MdbUtils.executeQuery(filePath, ksQkbdbSql + " limit " + groupSize * i + "," + groupSize, Ksqkbdb.class);
                if (!ksQkbdbList.isEmpty()) {
                    //List<Ksqkbdb> oldKsQkbdbList = iKsqkbdbService.list();
                    if (!oldKsQkbdbList.isEmpty()) {
                        List<Ksqkbdb> newKsList = new ArrayList<>();
                        for (Ksqkbdb ksbd : ksQkbdbList) {
                            for (Ksqkbdb old : oldKsQkbdbList) {
                                if (ksbd.getNd() != null && old.getNd() != null && ksbd.getXdjflbh() != null && old.getXdjflbh() != null
                                        && ksbd.getXkqbh() != null && old.getXkqbh() != null && ksbd.getXksbh() != null && old.getXksbh() != null
                                        && ksbd.getYksbh() != null && old.getYksbh() != null && ksbd.getYkqbh() != null && old.getYkqbh() != null
                                        && ksbd.getYdjflbh() != null && old.getYdjflbh() != null && ksbd.getXxzqdm() != null && old.getXxzqdm() != null
                                        && ksbd.getYxzqdm() != null && old.getYxzqdm() != null) {


                                    if (ksbd.getNd().equals(old.getNd()) && ksbd.getXdjflbh().equals(old.getXdjflbh()) &&
                                            ksbd.getXkqbh().equals(old.getXkqbh()) && ksbd.getXksbh().equals(old.getXksbh()) &&
                                            ksbd.getYkqbh().equals(old.getYkqbh()) && ksbd.getYksbh().equals(old.getYksbh()) &&
                                            ksbd.getYdjflbh().equals(old.getYdjflbh()) && ksbd.getXxzqdm().equals(old.getXxzqdm()) &&
                                            ksbd.getYxzqdm().equals(old.getYxzqdm())) {
                                        newKsList.add(ksbd);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            ksQkbdbList = ksQkbdbList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iKsqkbdbService.saveBatch(ksQkbdbList, groupSize);
                }
            }

            if (ksQkbdbrem > 0) {
                List<Ksqkbdb> ksQkbdbList = MdbUtils.executeQuery(filePath, ksQkbdbSql + " limit " + groupSize * ksQkbdbGroup + "," + ksQkbdbrem, Ksqkbdb.class);
                if (!ksQkbdbList.isEmpty()) {
                    // List<Ksqkbdb> oldKsQkbdbList = iKsqkbdbService.list();
                    if (!oldKsQkbdbList.isEmpty()) {
                        List<Ksqkbdb> newKsList = new ArrayList<>();
                        for (Ksqkbdb ksbd : ksQkbdbList) {
                            for (Ksqkbdb old : oldKsQkbdbList) {
                                if (ksbd.getNd() != null && old.getNd() != null && ksbd.getXdjflbh() != null && old.getXdjflbh() != null
                                        && ksbd.getXkqbh() != null && old.getXkqbh() != null && ksbd.getXksbh() != null && old.getXksbh() != null
                                        && ksbd.getYksbh() != null && old.getYksbh() != null && ksbd.getYkqbh() != null && old.getYkqbh() != null
                                        && ksbd.getYdjflbh() != null && old.getYdjflbh() != null && ksbd.getXxzqdm() != null && old.getXxzqdm() != null
                                        && ksbd.getYxzqdm() != null && old.getYxzqdm() != null) {


                                    if (ksbd.getNd().equals(old.getNd()) && ksbd.getXdjflbh().equals(old.getXdjflbh()) &&
                                            ksbd.getXkqbh().equals(old.getXkqbh()) && ksbd.getXksbh().equals(old.getXksbh()) &&
                                            ksbd.getYkqbh().equals(old.getYkqbh()) && ksbd.getYksbh().equals(old.getYksbh()) &&
                                            ksbd.getYdjflbh().equals(old.getYdjflbh()) && ksbd.getXxzqdm().equals(old.getXxzqdm()) &&
                                            ksbd.getYxzqdm().equals(old.getYxzqdm())) {
                                        newKsList.add(ksbd);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            ksQkbdbList = ksQkbdbList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iKsqkbdbService.saveBatch(ksQkbdbList, groupSize);
                }
            }
            System.out.println("矿山变动表 kcl_ksqkbdb 成功导入" + ksQkbdbCount + " 条");


            //压覆kcl_yf
            String yfSql = "select * from kcl_yf";
            String sql9 = "select count(*) from kcl_yf";
            if (nd != null) {
                yfSql = yfSql + " where nd = " + nd;
                sql9 = sql9 + " where nd = " + nd;
            }

            int yfCount = MdbUtils.selectCountr(filePath, sql9);
            int yfGroup = yfCount / groupSize;
            int yfrem = yfCount % groupSize;

            QueryWrapper<Yf> queryWrapperYf = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperYf.eq("nd", nd);
            }
            List<Yf> oldYfList = iYfService.selectList(queryWrapperYf);

            for (int i = 0; i < yfGroup; i++) {
                List<Yf> yfList = MdbUtils.executeQuery(filePath, yfSql + " limit " + groupSize * i + "," + groupSize, Yf.class);
                if (!yfList.isEmpty()) {
                    // List<Yf> oldYfList = iYfService.list();
                    if (!oldYfList.isEmpty()) {
                        List<Yf> newKsList = new ArrayList<>();
                        for (Yf yf : yfList) {
                            for (Yf old : oldYfList) {
                                if (yf.getNd() != null && old.getNd() != null && yf.getDjflbh() != null && old.getDjflbh() != null && yf.getKqbh() != null && old.getKqbh() != null) {
                                    if (yf.getNd().equals(old.getNd()) &&
                                            yf.getDjflbh().equals(old.getDjflbh()) &&
                                            yf.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(yf);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            yfList = yfList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iYfService.saveBatch(yfList, groupSize);
                }
            }
            if (yfrem > 0) {
                List<Yf> yfList = MdbUtils.executeQuery(filePath, yfSql + " limit " + groupSize * yfGroup + "," + yfrem, Yf.class);
                if (!yfList.isEmpty()) {
                    // List<Yf> oldYfList = iYfService.list();
                    if (!oldYfList.isEmpty()) {
                        List<Yf> newKsList = new ArrayList<>();
                        for (Yf yf : yfList) {
                            for (Yf old : oldYfList) {
                                if (yf.getNd() != null && old.getNd() != null && yf.getDjflbh() != null && old.getDjflbh() != null && yf.getKqbh() != null && old.getKqbh() != null) {
                                    if (yf.getNd().equals(old.getNd()) &&
                                            yf.getDjflbh().equals(old.getDjflbh()) &&
                                            yf.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(yf);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            yfList = yfList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iYfService.saveBatch(yfList, groupSize);
                }
            }

            System.out.println("压覆表 kcl_yf 成功导入" + yfCount + " 条");


            //坐标kcl_zb
            String zbSql = "select * from kcl_zb";
            String sql10 = "select count(*) from kcl_zb";
            if (nd != null) {
                zbSql = zbSql + " where nd = " + nd;
                sql10 = sql10 + " where nd = " + nd;
            }

            int zbCount = MdbUtils.selectCountr(filePath, sql10);
            int zbGroup = zbCount / groupSize;
            int zbrem = zbCount % groupSize;

            QueryWrapper<Zb> queryWrapperZb = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperZb.eq("nd", nd);
            }
            List<Zb> oldZbList = iZbService.selectList(queryWrapperZb);

            for (int i = 0; i < zbGroup; i++) {
                List<Zb> zbList = MdbUtils.executeQuery(filePath, zbSql + " limit " + groupSize * i + "," + groupSize, Zb.class);
                if (!zbList.isEmpty()) {
                    // List<Zb> oldZbList = iZbService.list();
                    if (!oldZbList.isEmpty()) {
                        List<Zb> newKsList = new ArrayList<>();
                        for (Zb zb : zbList) {
                            for (Zb old : oldZbList) {
                                if (zb.getNd() != null && old.getNd() != null && zb.getDjflbh() != null && old.getDjflbh() != null && zb.getKqbh() != null && old.getKqbh() != null) {
                                    if (zb.getNd().equals(old.getNd()) &&
                                            zb.getDjflbh().equals(old.getDjflbh()) &&
                                            zb.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(zb);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            zbList = zbList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iZbService.saveBatch(zbList, groupSize);
                }

            }
            if (zbrem > 0) {
                List<Zb> zbList = MdbUtils.executeQuery(filePath, zbSql + " limit " + groupSize * zbGroup + "," + zbrem, Zb.class);
                if (!zbList.isEmpty()) {
                    //  List<Zb> oldZbList = iZbService.list();
                    if (!oldZbList.isEmpty()) {
                        List<Zb> newKsList = new ArrayList<>();
                        for (Zb zb : zbList) {
                            for (Zb old : oldZbList) {
                                if (zb.getNd() != null && old.getNd() != null && zb.getDjflbh() != null && old.getDjflbh() != null && zb.getKqbh() != null && old.getKqbh() != null) {
                                    if (zb.getNd().equals(old.getNd()) &&
                                            zb.getDjflbh().equals(old.getDjflbh()) &&
                                            zb.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(zb);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            zbList = zbList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iZbService.saveBatch(zbList, groupSize);
                }
            }

            System.out.println("坐标表 kcl_zb 成功导入" + zbCount + " 条");

            //综合回收kcl_zhhs
            String zhhsSql = "select * from kcl_zhhs";
            String sql11 = "select count(*) from kcl_zhhs";
            if (nd != null) {
                zhhsSql = zhhsSql + " where nd = " + nd;
                sql11 = sql11 + " where nd = " + nd;
            }

            int zhhsCount = MdbUtils.selectCountr(filePath, sql11);
            int zhhsGroup = zhhsCount / groupSize;
            int zhhsrem = zhhsCount % groupSize;

            QueryWrapper<Zhhs> queryWrapperZhhs = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperZhhs.eq("nd", nd);
            }
            List<Zhhs> oldZhhsList = iZhhsService.selectList(queryWrapperZhhs);

            for (int i = 0; i < zhhsGroup; i++) {
                List<Zhhs> zhhsList = MdbUtils.executeQuery(filePath, zhhsSql + " limit " + groupSize * i + "," + groupSize, Zhhs.class);
                if (!zhhsList.isEmpty()) {
                    // List<Zhhs> oldZhhsList = iZhhsService.list();
                    if (!oldZhhsList.isEmpty()) {
                        List<Zhhs> newKsList = new ArrayList<>();
                        for (Zhhs zhhs : zhhsList) {
                            for (Zhhs old : oldZhhsList) {

                                if (zhhs.getNd() != null && old.getNd() != null && zhhs.getDjflbh() != null && old.getDjflbh() != null && zhhs.getKqbh() != null && old.getKqbh() != null) {
                                    if (zhhs.getNd().equals(old.getNd()) &&
                                            zhhs.getDjflbh().equals(old.getDjflbh()) &&
                                            zhhs.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(zhhs);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            zhhsList = zhhsList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iZhhsService.saveBatch(zhhsList, groupSize);
                }

            }
            if (zhhsrem > 0) {
                List<Zhhs> zhhsList = MdbUtils.executeQuery(filePath, zhhsSql + " limit " + groupSize * zhhsGroup + "," + zhhsrem, Zhhs.class);
                if (!zhhsList.isEmpty()) {
                    // List<Zhhs> oldZhhsList = iZhhsService.list();
                    if (!oldZhhsList.isEmpty()) {
                        List<Zhhs> newKsList = new ArrayList<>();
                        for (Zhhs zhhs : zhhsList) {
                            for (Zhhs old : oldZhhsList) {
                                if (zhhs.getNd() != null && old.getNd() != null && zhhs.getDjflbh() != null && old.getDjflbh() != null && zhhs.getKqbh() != null && old.getKqbh() != null) {
                                    if (zhhs.getNd().equals(old.getNd()) &&
                                            zhhs.getDjflbh().equals(old.getDjflbh()) &&
                                            zhhs.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(zhhs);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            zhhsList = zhhsList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iZhhsService.saveBatch(zhhsList, groupSize);
                }

            }
            System.out.println("综合回收 kcl_zhhs 成功导入" + zhhsCount + " 条");

            //矿山质量kcl_zl
            String zlSql = "select * from kcl_zl";
            String sql12 = "select count(*) from kcl_zl";
            if (nd != null) {
                zlSql = zlSql + " where nd = " + nd;
                sql12 = sql12 + " where nd = " + nd;
            }

            int zlCount = MdbUtils.selectCountr(filePath, sql12);
            int zlGroup = zlCount / groupSize;
            int zlrem = zlCount % groupSize;

            QueryWrapper<Zl> queryWrapperZl = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperZl.eq("nd", nd);
            }
            List<Zl> oldZlList = iZlService.selectOldList(queryWrapperZl);

            for (int i = 0; i < zlGroup; i++) {
                List<Zl> zlList = MdbUtils.executeQuery(filePath, zlSql + " limit " + groupSize * i + "," + groupSize, Zl.class);
                if (!zlList.isEmpty()) {
                    // List<Zl> oldZlList = iZlService.list();
                    if (!oldZlList.isEmpty()) {
                        List<Zl> newKsList = new ArrayList<>();
                        for (Zl zl : zlList) {
                            for (Zl old : oldZlList) {
                                if (zl.getNd() != null && old.getNd() != null && zl.getDjflbh() != null && old.getDjflbh() != null && zl.getKqbh() != null && old.getKqbh() != null) {
                                    if (zl.getNd().equals(old.getNd()) &&
                                            zl.getDjflbh().equals(old.getDjflbh()) &&
                                            zl.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(zl);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            zlList = zlList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iZlService.saveBatch(zlList, groupSize);
                }
            }
            if (zlrem > 0) {
                List<Zl> zlList = MdbUtils.executeQuery(filePath, zlSql + " limit " + groupSize * zlGroup + "," + zlrem, Zl.class);
                if (!zlList.isEmpty()) {
                    //  List<Zl> oldZlList = iZlService.list();
                    if (!oldZlList.isEmpty()) {
                        List<Zl> newKsList = new ArrayList<>();
                        for (Zl zl : zlList) {
                            for (Zl old : oldZlList) {
                                if (zl.getNd() != null && old.getNd() != null && zl.getDjflbh() != null && old.getDjflbh() != null && zl.getKqbh() != null && old.getKqbh() != null) {
                                    if (zl.getNd().equals(old.getNd()) &&
                                            zl.getDjflbh().equals(old.getDjflbh()) &&
                                            zl.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(zl);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            zlList = zlList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                        }
                    }
                    iZlService.saveBatch(zlList, groupSize);
                }
            }

            System.out.println("质量 kcl_zl 成功导入" + zlCount + " 条");


            //资源储量 kcl_zycl
            String zyclSql = "select * from kcl_zycl";
            String sql13 = "select count(*) from kcl_zycl";
            if (nd != null) {
                zyclSql = zyclSql + " where nd = " + nd;
                sql13 = sql13 + " where nd = " + nd;
            }

            int zyclCount = MdbUtils.selectCountr(filePath, sql13);
            int zyclGroup = zyclCount / groupSize;
            int zyclrem = zyclCount % groupSize;

            QueryWrapper<Zycl> queryWrapperZycl = new QueryWrapper<>();
            if (nd != null) {
                queryWrapperZycl.eq("nd", nd);
            }
            List<Zycl> oldZyclList = iZyclService.selectList(queryWrapperZycl);

            for (int i = 0; i < zyclGroup; i++) {
                List<Zycl> zyclList = MdbUtils.executeQuery(filePath, zyclSql + " limit " + groupSize * i + "," + groupSize, Zycl.class);
                if (!zyclList.isEmpty()) {
                    //List<Zycl> oldZyclList = iZyclService.list();
                    if (!oldZyclList.isEmpty()) {
                        List<Zycl> newKsList = new ArrayList<>();
                        for (Zycl zycl : zyclList) {
                            for (Zycl old : oldZyclList) {
                                if (zycl.getNd() != null && old.getNd() != null && zycl.getDjflbh() != null && old.getDjflbh() != null && zycl.getKqbh() != null && old.getKqbh() != null) {
                                    if (zycl.getNd().equals(old.getNd()) &&
                                            zycl.getDjflbh().equals(old.getDjflbh()) &&
                                            zycl.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(zycl);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            zyclList = zyclList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                            if (zyclList.size() == 0) {
                                zyclList = MdbUtils.executeQuery(filePath, zyclSql, Zycl.class);
                            }
                        }
                    }
                    iZyclService.saveBatch(zyclList, groupSize);
                }
            }
            if (zyclrem > 0) {
                List<Zycl> zyclList = MdbUtils.executeQuery(filePath, zyclSql + " limit " + groupSize * zyclGroup + "," + zyclrem, Zycl.class);
                if (!zyclList.isEmpty()) {
                    // List<Zycl> oldZyclList = iZyclService.list();
                    if (!oldZyclList.isEmpty()) {
                        List<Zycl> newKsList = new ArrayList<>();
                        for (Zycl zycl : zyclList) {
                            for (Zycl old : oldZyclList) {
                                if (zycl.getNd() != null && old.getNd() != null && zycl.getDjflbh() != null && old.getDjflbh() != null && zycl.getKqbh() != null && old.getKqbh() != null) {
                                    if (zycl.getNd().equals(old.getNd()) &&
                                            zycl.getDjflbh().equals(old.getDjflbh()) &&
                                            zycl.getKqbh().equals(old.getKqbh())) {
                                        newKsList.add(zycl);
                                    }
                                }
                            }
                        }
                        if (!newKsList.isEmpty()) {
                            zyclList = zyclList.stream().filter(ks -> !newKsList.contains(ks)).collect(Collectors.toList());
                            if (zyclList.size() == 0) {
                                zyclList = MdbUtils.executeQuery(filePath, zyclSql, Zycl.class);
                            }
                        }
                    }
                    iZyclService.saveBatch(zyclList, groupSize);
                }
            }


            System.out.println("资源储量 kcl_zycl 成功导入" + zyclCount + " 条");


        } catch (Exception e) {
            System.out.println("mdb导入报错:" + e);
            e.printStackTrace();
        }

    }


    /**
     * mdb文件导入
     *
     * @param filePath
     * @param nd
     * @return
     */
    @Override
    public void tbData(String filePath, Integer nd) {
        try{
            //同步mdb数据
            synchronizingMdbData2(filePath, nd);
            // 同步矿山数据
            iKsZyclhzService.synchronizingKsDataNd(nd.toString());
        }catch (Exception e){
            throw new CommonException("mdb导入失败");
        }

    }

    /**
     * 同步mdb数据
     */
    public void synchronizingMdbData(String filePath, Integer nd) {
        int groupSize = 50; // 每组的数据量
        // 构建最终的SQL语句和参数列表
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            List<String> tables = tableFieldsMapper.getTables();
            if (tables == null || tables.isEmpty()) {
                return;
            }
            // 存储所有Future对象
            List<Future<?>> futures = new CopyOnWriteArrayList<>();
            // 打开MDB文件并准备读取数据
            try (Connection mdbConnection = MdbUtils.openMdbConnection(filePath)) {
                for (String table : tables) {
                    System.out.println(table + ": 开始同步数据！！！");

                    // 1. 先根据表名删除表数据
                    String delSql = "DELETE FROM " + table + " WHERE nd= " + nd;
                    try {
                        Integer count =  tableFieldsMapper.delData(delSql);
                        System.out.println("删除"+count+"条"+table+"数据");
                    } catch (Exception e) {
                        System.err.println("Error deleting data from table " + table + ": " + e.getMessage());
                        return;
                    }

                    // 从MDB文件中读取当前表的数据
                    List<Map<String, Object>> tableData = MdbUtils.readTableData(mdbConnection, table,nd);
                    System.out.println(table + ": 共有 " + tableData.size() + " 条数据.");
                    if (!tableData.isEmpty()) {
                        // 将数据分组
                        List<List<Map<String, Object>>> dataGroups = Lists.partition(tableData, groupSize);
                        System.out.println(table + ": 共划分成 " + dataGroups.size() + " 组数据.");
                        // 提交每个小组的任务到线程池
                        for (List<Map<String, Object>> group : dataGroups) {
                            Connection finalConn = conn;
//                            Future<?> future = executorService.submit(() -> {
//                                try {
                                    //同步mdb数据
                                    insertRecord(table, group, finalConn);
//                                } catch (Exception e) {
//                                    throw new RuntimeException("Error during insertRecord: " + e.getMessage(), e);
//                                }
//                            });
//                            futures.add(future);
                        }
                    }
                    System.out.println(table + ": 成功同步数据 —— " + (tableData != null ? tableData.size() : 0));
                }
            }

            // 等待所有任务完成并检查是否有任何任务抛出异常
//            for (Future<?> future : futures) {
//                try {
//                    future.get(); // 如果任务抛出异常，这里会重新抛出
//                } catch (ExecutionException | InterruptedException e) {
//                    throw new ServiceException("Error during parallel processing: " + e.getCause().getMessage(), e.getCause());
//                }
//            }
        } catch (Exception e) {
            throw new ServiceException("mdb导入报错:" + e.getMessage(), e);
        } finally {
            // 关闭线程池
//            shutdownAndAwaitTermination(executorService);


        }
    }


    public void synchronizingMdbData2(String filePath, Integer nd) {
        int groupSize = 50; // 每组的数据量
        ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors() * 2);
        List<Future<?>> futures = new CopyOnWriteArrayList<>();


        try (Connection mdbConnection = MdbUtils.openMdbConnection(filePath)) {
            List<String> tables = tableFieldsMapper.getTables();
            if (tables == null || tables.isEmpty()) {
                return;
            }

            for (String table : tables) {
                System.out.println(table + ": 开始同步数据！！！");
                String delSql = "DELETE FROM " + table + " WHERE nd= " + nd;

                try {
                    Integer count = tableFieldsMapper.delData(delSql);
                    System.out.println("删除" + count + "条" + table + "数据");
                } catch (Exception e) {
                    System.err.println("Error deleting data from table " + table + ": " + e.getMessage());
                    return;
                }

                List<Map<String, Object>> tableData = MdbUtils.readTableData(mdbConnection, table, nd);
                System.out.println(table + ": 共有 " + tableData.size() + " 条数据.");
                if (!tableData.isEmpty()) {
                    List<List<Map<String, Object>>> dataGroups = Lists.partition(tableData, groupSize);
                    System.out.println(table + ": 共划分成 " + dataGroups.size() + " 组数据.");

                    for (List<Map<String, Object>> group : dataGroups) {
                        Future<?> future = executorService.submit(() -> {

                            Connection threadConn =null;
                            try {
                                // 从连接池获取连接
                                threadConn = DatabaseConnectionPool.getConnection();
                                //同步mdb数据
                                insertRecord(table, group, threadConn);
                            } catch (Exception e) {
                                throw new RuntimeException("Error during insertRecord: " + e.getMessage(), e);
                            } finally {
                                // 归还连接到连接池
                                if (threadConn != null) {
                                    try {
                                        threadConn.close();
                                    } catch (SQLException e) {
                                        e.printStackTrace();
                                    }
                                }
                            }
                        });
                        futures.add(future);
                    }
                }
                System.out.println(table + ": 成功同步数据 —— " + (tableData != null ? tableData.size() : 0));
            }

            // 等待所有任务完成
            for (Future<?> future : futures) {
                try {
                    future.get();
                } catch (ExecutionException | InterruptedException e) {
                    throw new ServiceException("Error during parallel processing: " + e.getCause().getMessage(), e.getCause());
                }
            }
        } catch (Exception e) {
            throw new ServiceException("mdb导入报错:" + e.getMessage(), e);
        } finally {
            shutdownAndAwaitTermination(executorService);
            // 关闭连接池
            DatabaseConnectionPool.closeDataSource();
        }
    }

    /**
     * 等待线程池终止
     *
     * @param pool
     */
    private void shutdownAndAwaitTermination(ExecutorService pool) {
        pool.shutdown(); // Disable new tasks from being submitted
        try {
            // Wait a while for existing tasks to terminate
            if (!pool.awaitTermination(60, TimeUnit.SECONDS)) {
                pool.shutdownNow(); // Cancel currently executing tasks
                // Wait a while for tasks to respond to being cancelled
                if (!pool.awaitTermination(60, TimeUnit.SECONDS))
                    System.err.println("Pool did not terminate");
            }
        } catch (InterruptedException ie) {
            // (Re-)Cancel if current thread also interrupted
            pool.shutdownNow();
            // Preserve interrupt status
            Thread.currentThread().interrupt();
        }
    }

    /**
     * 同步mdb数据（目前只做替换，不做任何更新）
     *
     * @param table
     * @param tableData
     */
    public void insertRecord(String table, List<Map<String, Object>> tableData,Connection conn) {
        if (tableData == null || tableData.isEmpty()) {
            System.out.println("No data to insert for table: " + table);
            return;
        }

        // 获取字段信息
        List<FieldVo> fieldList = tableFieldsMapper.getField(table);
        if (fieldList.isEmpty()) {
            System.out.println("No fields found for table: " + table);
            return;
        }
        // 构建列名部分
        StringBuilder columns = new StringBuilder();
        StringBuilder placeholders = new StringBuilder();
        for (FieldVo field : fieldList) {
            if (columns.length() > 0) {
                columns.append(", ");
                placeholders.append(", ");
            }
            columns.append(field.getField());
            placeholders.append("?");
        }

        // 开始构建SQL语句
        String sql = "INSERT INTO " + table + " (" + columns + ") VALUES (" + placeholders + ")";

        // 准备批量插入的值
        List<List<Object>> batchValues = new ArrayList<>();
        for (Map<String, Object> record : tableData) {
            List<Object> values = new ArrayList<>();
            for (FieldVo field : fieldList) {
                Object value = record.get(field.getField());
                if (value == null) {
                    values.add(null);
                } else {
                    if ("0".equals(field.getDataType())) { // 字符串类型
                        values.add(value.toString());
                    } else if ("1".equals(field.getDataType())) { // 数字类型
                        values.add(value);
                    }
                }
            }
            batchValues.add(values);
        }


        try (
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 开启事务
      //  conn.setAutoCommit(false);

            // 设置批量插入的参数
            for (List<Object> values : batchValues) {
                int index = 1; // PreparedStatement参数索引从1开始
                for (Object value : values) {
                    if (value == null) {
                        pstmt.setNull(index, java.sql.Types.NULL);
                    } else {
                        if ("0".equals(fieldList.get(index - 1).getDataType())) {
                            pstmt.setString(index, (String) value);
                        } else if ("1".equals(fieldList.get(index - 1).getDataType())) {
                            pstmt.setObject(index, value);
                        }
                    }
                    index++;
                }
                pstmt.addBatch();
            }

            // 执行批量插入
            pstmt.executeBatch();
            System.out.println("批量插入成功:" + table + ":" + batchValues.size() + "条");
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("Error inserting data into table " + table + ": " + e.getMessage());
            // 回滚事务
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
        }
    }


    /**
     * mdb文件导入
     *
     * @param files
     * @param sjgl
     * @return
     */

    @Override
    public String fileUploadMdb(List<MultipartFile> files, Sjgl sjgl) {
        String result = "";
        for (MultipartFile file : files) {
            //文件名称
            String fileName = file.getOriginalFilename();
            fileName = URLDecoder.decode(fileName, StandardCharsets.UTF_8);//解码
            String suffix = fileName.substring(fileName.lastIndexOf("."));
            // String wjName = fileName.substring(0, fileName.lastIndexOf("."));

            File file1 = new File(baseFilePath+ File.separator + sjgl.getMc() + suffix);
            if (!suffix.toLowerCase().equals(".mdb")) {
                result += "[" + fileName + "]文件格式错误";
            } else {
                try {
                    FileUtils.copyInputStreamToFile(file.getInputStream(), file1);
                    tbData(file1.getPath(), sjgl.getNd());
                } catch (Exception e) {
                    result = String.valueOf(e);
                    System.out.println(e);
                }
                sjgl.setMc(file1.getName());
                Boolean ifSuccess = iSjglService.save(sjgl);
                if (ifSuccess) {
                    result = "[" + file1.getName() + "]" + "数据导入成功";
                }

            }

        }

        return result;
    }

    @Override
    public String dataExportMdb(String fileName, Integer nd, String tableName2) {
        try {

            String path = "D:\\WaterWork\\SKY_mineral_life_cycle\\sky_mineral_reserves_life_cycle\\snowy-plugin\\snowy-plugin-smzq\\target\\classes\\upload\\拉拉.mdb";
            File file = new File(path);


            //读取表名
            List<ExportTable> tableList = JSONUtil.toList(ResourceUtil.readUtf8Str("json" + File.separator + "exportTable.json"), ExportTable.class);
            //PathUtils.singleFileShow(path,response);
            // System.out.println(PathUtils.getUpLoadPath() + File.separator + fileName + ".mdb");
            convertPgTableToFile(tableList, file, 2021);
        } catch (Exception e) {
            // 处理错误
        }
        return null;
    }

    /**
     * mdb文件导出
     *
     * @param tableList
     * @param mdbFilePath
     * @param nd
     * @return
     */
    @Override
    public Boolean convertPgTableToFile(List<ExportTable> tableList, File mdbFilePath, Integer nd) {
        boolean ifSuccess = true;
        try {
            Connection pgConn = jdbcTemplate.getDataSource().getConnection();
            Statement stmt = pgConn.createStatement();
            Database mdb = DatabaseBuilder.create(Database.FileFormat.V2000, mdbFilePath);
//
//            //动态读取表名
//            pgTableNames = new ArrayList<>();
//            DatabaseMetaData meta = pgConn.getMetaData();
//            ResultSet tablesRs = meta.getTables(null, null, "%", new String[]{"TABLE"});
//            while (tablesRs.next()) {
//                // 获取表名
//                String tableName = tablesRs.getString("table_name");
//                // 根据年度字段过滤表名
//                if (tableName.startsWith("kcl_")) {
//                    ResultSet resultSet = meta.getColumns(null, null, tableName, "nd");
//                    if (resultSet.next()) {
//                        pgTableNames.add(tableName);
//                    }
//                }
//            }


            for (ExportTable pgTableName : tableList) {
                PreparedStatement preparedStatement = null;
                if (pgTableName.isIncludeNd()) {
                    preparedStatement = pgConn.prepareStatement("SELECT * FROM " + pgTableName.getTableName() + " where nd = " + nd);
                } else {

                    preparedStatement = pgConn.prepareStatement("SELECT * FROM " + pgTableName.getTableName());
                }
                /*preparedStatement.setInt(1, nd);*/
                ResultSet rs = preparedStatement.executeQuery();
                ResultSetMetaData metaData = rs.getMetaData();
                List<ColumnBuilder> columnDefs = new ArrayList<>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnName = metaData.getColumnName(i);
                    int jdbcType = metaData.getColumnType(i);
                    DataType dataType = mapJdbcTypeToAccess(jdbcType);
                    columnDefs.add(new ColumnBuilder(columnName, dataType));
                }

                Table table = new TableBuilder(pgTableName.getTableName()).addColumns(columnDefs).toTable(mdb);

                while (rs.next()) {
                    List<Object> rowData = new ArrayList<>();
                    for (int i = 1; i <= metaData.getColumnCount(); i++) {
                        rowData.add(rs.getObject(i));
                    }
                    table.addRow(rowData.toArray());
                }
            }

            mdb.flush();
            mdb.close();

        } catch (Exception e) {
            ifSuccess = false;
            e.printStackTrace();
        }
        return ifSuccess;
    }

    /**
     * 将PostgreSQL中的多个表数据导出到一个新的MDB文件中。
     *
     * @param tableList 包含需要导出的表信息的列表
     * @param mdbFilePath 目标MDB文件路径
     * @param nd 可能用于过滤或标识的参数（根据实际情况使用）
     * @return 操作是否成功
     */

    @Override
    public Boolean convertPgTableToFileNew(List<ExportTable> tableList, String mdbFilePath, Integer nd) {
        boolean ifSuccess = true;

        // 替换反斜杠为正斜杠
        mdbFilePath = mdbFilePath.replace("\\", "/");

        // 构建MDB数据库连接字符串。
        String mdbUrl = "jdbc:ucanaccess://" + mdbFilePath + ";newdatabaseversion=V2007";

        try (Connection pgConn = DriverManager.getConnection(DB_URL, USER, PASS);
             Connection mdbConn = DriverManager.getConnection(mdbUrl)) {

            for (ExportTable exportTable : tableList) {
                String pgTableName = exportTable.getTableName();

                List<String> columns = new ArrayList<>();
                List<String> types = new ArrayList<>();

                // Step 1: 查询PostgreSQL表中的列名和类型
                String queryColumns = "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = ?";
                try (PreparedStatement stmtColumns = pgConn.prepareStatement(queryColumns)) {
                    stmtColumns.setString(1, pgTableName);
                    ResultSet rsColumns = stmtColumns.executeQuery();
                    while (rsColumns.next()) {
                        columns.add(rsColumns.getString("column_name"));
                        types.add(convertPgTypeToMdbType(rsColumns.getString("data_type"))); // 转换为MDB支持的数据类型
                    }
                }

                // Step 2: 查询表数据，根据是否包含年度字段动态生成查询条件
                StringBuilder queryPg = new StringBuilder("SELECT ");
                for (int i = 0; i < columns.size(); i++) {
                    if (i > 0) queryPg.append(", ");
                    queryPg.append(columns.get(i));
                }
                queryPg.append(" FROM ").append(pgTableName);

                // 根据是否需要年度过滤添加WHERE子句
                if (exportTable.isIncludeNd()) {
                    queryPg.append(" WHERE nd = ").append(nd);
                }

                Statement stmtPg = pgConn.createStatement();
                ResultSet rsData = stmtPg.executeQuery(queryPg.toString());

                // Step 3: 在MDB文件中创建对应的表前，先检查并删除已存在的同名表
                DatabaseMetaData dbmd = mdbConn.getMetaData();
                ResultSet rsTbl = dbmd.getTables(null, null, pgTableName, new String[]{"TABLE"});
                if (rsTbl.next()) {
                    // 表存在，删除旧表
                    String dropTableSql = "DROP TABLE " + pgTableName;
                    try (Statement stmtDrop = mdbConn.createStatement()) {
                        stmtDrop.execute(dropTableSql);
                    } catch (SQLException e) {
                        System.err.println("Failed to drop existing table: " + pgTableName);
                        e.printStackTrace();
                        continue; // 或者选择其他方式处理错误
                    }
                }

                // 创建新表
                StringBuilder createTableSql = new StringBuilder("CREATE TABLE ").append(pgTableName).append(" (");
                for (int i = 0; i < columns.size(); i++) {
                    if (i > 0) createTableSql.append(", ");
                    createTableSql.append(columns.get(i)).append(" ").append(types.get(i));
                }
                createTableSql.append(")");
                try (Statement stmtMdb = mdbConn.createStatement()) {
                    stmtMdb.execute(createTableSql.toString());
                } catch (SQLException e) {
                    System.err.println("Failed to create table: " + pgTableName);
                    e.printStackTrace();
                    continue; // 或者选择其他方式处理错误
                }

                // Step 4: 将数据插入到新创建的MDB表中
                StringBuilder insertMdb = new StringBuilder("INSERT INTO ").append(pgTableName).append(" (");
                for (int i = 0; i < columns.size(); i++) {
                    if (i > 0) insertMdb.append(", ");
                    insertMdb.append(columns.get(i));
                }
                insertMdb.append(") VALUES (");
                for (int i = 0; i < columns.size(); i++) {
                    if (i > 0) insertMdb.append(", ");
                    insertMdb.append("?");
                }
                insertMdb.append(")");

                try (PreparedStatement pstmtMdb = mdbConn.prepareStatement(insertMdb.toString())) {
                    while (rsData.next()) {
                        for (int i = 0; i < columns.size(); i++) {
                            setPreparedStatementValue(pstmtMdb, i + 1, rsData, columns.get(i), types.get(i)); // 设置PreparedStatement的值
                        }
                        pstmtMdb.addBatch(); // 添加到批量操作
                    }
                    pstmtMdb.executeBatch(); // 执行批量插入
                }

                System.out.println("表的数据导出成功完成: " + pgTableName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            ifSuccess = false; // 如果发生异常，标记操作失败
        }
        return ifSuccess;
    }

    /**
     * 将PostgreSQL的数据类型转换为MDB支持的数据类型。
     *
     * @param pgType PostgreSQL的数据类型
     * @return MDB支持的数据类型
     */
    private String convertPgTypeToMdbType(String pgType) {
        switch (pgType.toLowerCase()) {
            case "integer":
            case "bigint":
            case "smallint":
                return "INTEGER";
            case "double precision":
                return "DOUBLE";
            case "real":
                return "SINGLE"; // Access中对应Single Precision Floating Point
            case "character varying":
            case "text":
                return "TEXT";
            case "boolean":
                return "BIT";
            case "date":
            case "timestamp without time zone":
                return "DATETIME";
            case "time without time zone":
                return "TIME";
            default:
                return "TEXT"; // 默认转换为文本类型
        }
    }

    /**
     * 根据字段类型设置PreparedStatement的值。
     *
     * @param pstmt PreparedStatement对象
     * @param index 参数索引
     * @param rs 结果集
     * @param columnName 列名
     * @param type 数据类型
     * @throws SQLException 抛出SQL异常
     */
    private void setPreparedStatementValue(PreparedStatement pstmt, int index, ResultSet rs, String columnName, String type) throws SQLException {
        if ("INTEGER".equalsIgnoreCase(type)) {
            pstmt.setInt(index, rs.getInt(columnName));
        } else if ("LONG".equalsIgnoreCase(type)) {
            // 对于Access数据库，实际上可能不需要单独处理LONG，因为通常使用INTEGER即可满足需求。
            // 如果确实需要区分，可以根据实际情况调整。
            pstmt.setLong(index, rs.getLong(columnName));
        } else if ("TEXT".equalsIgnoreCase(type)) {
            pstmt.setString(index, rs.getString(columnName));
        } else if ("BIT".equalsIgnoreCase(type)) {
            pstmt.setBoolean(index, rs.getBoolean(columnName));
        } else if ("DOUBLE".equalsIgnoreCase(type)) {
            pstmt.setDouble(index, rs.getDouble(columnName));
        } else if ("SINGLE".equalsIgnoreCase(type)) {
            // SINGLE在Java中没有直接对应的set方法，可以使用setFloat代替。
            pstmt.setFloat(index, rs.getFloat(columnName));
        } else if ("DATETIME".equalsIgnoreCase(type)) {
            // 使用Timestamp来存储日期时间类型
            Timestamp timestamp = rs.getTimestamp(columnName);
            if (timestamp != null) {
                pstmt.setTimestamp(index, timestamp);
            } else {
                pstmt.setNull(index, Types.TIMESTAMP);
            }
        } else if ("TIME".equalsIgnoreCase(type)) {
            Time time = rs.getTime(columnName);
            if (time != null) {
                pstmt.setTime(index, time);
            } else {
                pstmt.setNull(index, Types.TIME);
            }
        } else {
            // 默认情况下，尝试将值作为字符串处理
            String value = rs.getString(columnName);
            if (rs.wasNull()) {
                pstmt.setNull(index, Types.VARCHAR);
            } else {
                pstmt.setString(index, value);
            }
        }
    }

    private static DataType mapJdbcTypeToAccess(int jdbcType) {
        // 这里简单映射，实际需要更完整的类型转换规则
        switch (jdbcType) {
            case Types.VARCHAR:
            case Types.CHAR:
//                return DataType.TEXT;
                return DataType.MEMO;//Access数据库的TEXT类型最大255，改用MEMO类型
            case Types.INTEGER:
                return DataType.LONG;
            // 其他类型转换...
            default:
                return DataType.TEXT;
        }
    }

    @Override
    public void getnow() {
        String sql = "kcl_zycl,kcl_zl,kcl_zhhs,kcl_zb,kcl_yf,kcl_ksqkbdb,kcl_ksjj,kcl_ks,kcl_kcp,kcl_cxh,kcl_bg,kcl_bazycl";
        List<String> list = Arrays.asList(sql.split(","));
        for (String s : list) {
            getData(s);
        }
    }


    /**根据模版导出mdb文件
     *
     * @param nd
     * @param outputPath
     * @return
     */
    @Override
    public boolean exportMdb(Integer nd, String outputPath) {

        List<String> tables = tableFieldsMapper.getTables();
        if (tables == null || tables.isEmpty()) {
            return false;
        }

        String templateFilePath = PathUtils.getNbfbPath(13);
        // 使用ClassPathResource加载模板文件
        ClassPathResource classPathResource = new ClassPathResource(templateFilePath);

        try (InputStream inputStream = classPathResource.getInputStream();
             OutputStream outputStream = new FileOutputStream(outputPath)) {

            // 缓冲区大小可以调整以优化性能
            byte[] buffer = new byte[1024];
            int bytesRead;

            // 读取输入流并写入输出流
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }

            System.out.println("文件已成功复制到: " + outputPath);
        } catch (IOException e) {
            System.err.println("复制文件时发生错误: " + e.getMessage());
        }

        // 打开新的MDB文件进行写入
        String mdbUrl = "jdbc:ucanaccess://" + outputPath + ";newdatabaseversion=V2007";
        try (Connection mdbConn = DriverManager.getConnection(mdbUrl)) {
            for (String table : tables) {
                // 查询PostgreSQL中的数据
                List<Map> data = queryDataFromPg(table, nd);

                if (data == null || data.isEmpty()) continue;

                // 插入数据到MDB
                insertDataIntoMDB(table, data, mdbConn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return true;
    }


    /**
     * 根据表名查询数据
     * @param tableName
     * @param nd
     * @return
     */
    private List<Map> queryDataFromPg(String tableName, int nd) {
        // 实现查询逻辑，返回结果集
        // 例如：SELECT * FROM tableName WHERE nd = ?
        // 返回一个List<List<Object>>, 其中每个内部List代表一行数据


        return tableFieldsMapper.getTableDataList(tableName, nd); // 这里需要你的具体实现
    }

    /**
     * 根据查询的数据插入mdb
     * @param tableName
     * @param data
     * @param mdbConn
     * @throws SQLException
     */
    private void insertDataIntoMDB(String tableName, List<Map> data, Connection mdbConn) throws SQLException {
        // 获取数据库元数据
        DatabaseMetaData dbmd = mdbConn.getMetaData();

        // 检查表是否存在
        boolean tableExists = false;
        ResultSet rsTables = dbmd.getTables(null, null, tableName, new String[]{"TABLE"});
        if (rsTables.next()) {
            tableExists = true;
        }
        rsTables.close();

        if (!tableExists) {
            throw new SQLException("Table " + tableName + " does not exist in the database.");
        }

        // 获取表结构信息
        StringBuilder columns = new StringBuilder();
        List<String> columnNames = new ArrayList<>();

        ResultSet rsCols = dbmd.getColumns(null, null, tableName, null);
        while (rsCols.next()) {
            String columnName = rsCols.getString("COLUMN_NAME");
            if (columns.length() > 0) columns.append(", ");
            columns.append(columnName);
            columnNames.add(columnName.toLowerCase(Locale.ROOT));  // 添加到列表中并转换为小写
        }
        rsCols.close();

        // 构建INSERT语句
        String sql = "INSERT INTO " + tableName + " (" + columns.toString() + ") VALUES (";
        for (int i = 0; i < columnNames.size(); i++) {
            if (i > 0) sql += ", ";
            sql += "?";  // 使用占位符
        }
        sql += ")";

        try (PreparedStatement pstmt = mdbConn.prepareStatement(sql)) {
            for (Map  row : data) {
                int paramIndex = 1;
                for (String columnName : columnNames) {
                    Object value = row.get(columnName.toLowerCase(Locale.ROOT));
                    if (value == null) {
                        pstmt.setNull(paramIndex, Types.VARCHAR);  // 如果值为空，则设置为NULL
                    } else if (value instanceof Integer) {
                        pstmt.setInt(paramIndex, (Integer) value);
                    } else if (value instanceof String) {
                        pstmt.setString(paramIndex, (String) value);
                    } else if (value instanceof Double){
                        pstmt.setDouble(paramIndex, (Double) value);
                    }else if( value instanceof Long){
                        pstmt.setLong(paramIndex, (Long) value);
                    }else if( value instanceof Float){
                        pstmt.setFloat(paramIndex, (Float) value);
                    }else if (value instanceof Timestamp){
                        pstmt.setTimestamp(paramIndex, (Timestamp) value);
                    }else if (value instanceof Boolean){
                        pstmt.setBoolean(paramIndex, (Boolean) value);
                    }
                    // 添加其他类型的处理...
                    else {
                        throw new SQLException("Unsupported type: " + value.getClass().getName());
                    }
                    paramIndex++;
                }
                pstmt.addBatch();  // 将当前行添加到批处理
            }
            pstmt.executeBatch();  // 执行批处理
        } catch (SQLException e) {
            System.err.println("SQL Exception occurred: " + e.getMessage());
            throw e;
        }
    }

    /**
     * 根据表名获取表字段
     *
     * @param tableName 表名称
     * @return
     */
    public void getData(String tableName) {
        String DB_URL = "jdbc:postgresql://localhost:5432/ks_smzq?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        String USER = "root";
        String PASSWORD = "root";
        String sql = "select ";
        String sqlAdd = "INSERT into public." + tableName + "(";


        try {
            Connection connection = DriverManager.getConnection(DB_URL, USER, PASSWORD);
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columnsRs = metaData.getColumns(null, null, tableName, null);

            while (columnsRs.next()) {
                String columnName = columnsRs.getString("COLUMN_NAME");
//                String columnType = columnsRs.getString("TYPE_NAME");
//                String columnComment = getCommentForColumn(connection, tableName, columnName);
//                if (!columnComment .equals("null") ){
                if (!columnName.equals("pkid")) {
                    sqlAdd += columnName + ",";
                    sql += "\"" + columnName + "\",";
                }

//                }
            }

            sqlAdd = sqlAdd.substring(0, sqlAdd.lastIndexOf(",")) + ")";

            sql = sql.substring(0, sql.lastIndexOf(",")) + " from ";
            String table = "temp.\"" + tableName.toUpperCase(Locale.ROOT) + "\"";
            String daxiesql = sql.toUpperCase(Locale.ROOT);
            System.out.println(sqlAdd + " " + daxiesql + table);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }












}
